﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using DingTalk.Api;
using DingTalk.Api.Request;
using DingTalk.Api.Response;
using FastJSON;
using Microsoft.AspNetCore.Components;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using DingTida.Models;
using RouteAttribute = Microsoft.AspNetCore.Components.RouteAttribute;
using Microsoft.Extensions.Caching.Memory;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.AspNetCore.Authentication;
using Microsoft.Data.SqlClient;
using Microsoft.IdentityModel.Protocols;
using System.Data;
using System.IO;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Hosting.Internal;
using Microsoft.AspNetCore.Hosting;
using System.Globalization;
using DingTida.BLL;
using DingTida.IBLL;
using System.Threading;
namespace DingTida.Controllers
{
    [Route("api/[controller]")]
    // [ApiController]
    public class UserController : ControllerBase
    {
        public string access_token = "";
        public EasyStarContext db;
        public OapiUserGetuserinfoResponse rsp = null;
        public string name;
        public IMemoryCache Cache;
        private IWebHostEnvironment _webHostEnvironment;
        public IWorkFlowInstance wfi;
        public string userid = "";
        public IMsg msg;
        public UserController(EasyStarContext _db, IMemoryCache _cache, IWebHostEnvironment webHostEnvironment, IWorkFlowInstance _wfi, IMsg _msg)
        {
            this.db = _db;
            this.Cache = _cache;
            this._webHostEnvironment = webHostEnvironment;
            this.wfi = _wfi;
            this.msg = _msg;
        }


        [HttpPost]
        public IActionResult overtime(string staffid, string infos, string cont)
        {

            // cont 数据输入格式  2020-5-27-0-0-0-0

            string[] _date = cont.Split("-");
            string year = _date[0].PadLeft(2, '0');
            string month = _date[1].PadLeft(2, '0');
            string day = _date[2].PadLeft(2, '0');
            string bhour = _date[3].PadLeft(2, '0');
            string bmin = _date[4].PadLeft(2, '0');
            string ehour = _date[5].PadLeft(2, '0');
            string emin = _date[6].PadLeft(2, '0');




            #region  日期转换





            string dateString = year + month + day;
            string dateStringb = year + month + day + bhour + bmin;
            string dateStringe = year + month + day + ehour + emin;

            DateTime dt = DateTime.ParseExact(dateString, "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
            DateTime dtb = DateTime.ParseExact(dateStringb, "yyyyMMddHHmm", System.Globalization.CultureInfo.CurrentCulture);
            DateTime dte = DateTime.ParseExact(dateStringe, "yyyyMMddHHmm", System.Globalization.CultureInfo.CurrentCulture);

            #region 判断是否存在同样的申请


            #endregion
            var f = Request.Form.Files["img"];

            var days = dt.DayOfWeek;

            var emp = db.EmEmpData.Where(c => c.EmpNo == staffid).FirstOrDefault();
            WorkFlows wf = new WorkFlows();
            wf.Applydate = System.DateTime.Now;
            wf.Begindate = dtb;
            wf.Enddate = dte;
            wf.Reason = infos;



            MemoryStream ms = new MemoryStream();
            f.CopyTo(ms);
            ms.Flush(); //非常重要的信息  不刷进去 它里面是空的
            wf.Sign = ms.ToArray();
            string content = emp.Fname + " " + staffid + "申请加班" + "时间由" + dtb.ToString() + "到" + dte.ToString();
            #region 添加加班工作流实例
            string id = Guid.NewGuid().ToString();
            wf.Staffid = staffid;
            wf.FlowId = id;
            wf.FlowContent = content;
            wf.States = "发起";
            wf.Type = "加班";
            wf.IsComplete = false;

            var IsExit = db.WorkFlows.Any(c => c.Staffid == staffid && c.FlowContent == content);

            if (!IsExit)
            {
                #endregion
                if (emp.CardNo2 == "" || emp.CardNo2 == null)//如果不是经理级别
                {

                    var dept = db.EsDept.Where(c => c.FullCode == emp.Dept).FirstOrDefault();

                    #region 职员加班流程：判断是否时月薪如果是，  不需要生管同意，部门同意以后，直接部门到人事
                    if (emp.Jxfs == "月薪")
                    {



                        var nid = wfi.AddInstanceByManager(staffid, id);// 添加流转步骤一  转经理审批
                                                                        //msg.SendMsg("钉钉提示您，泰德小程序内OA项目 有一项待您核签，", staffid);


                        if (days == DayOfWeek.Sunday || days == DayOfWeek.Saturday)
                        {

                            wf.Remark = "假日加班";
                        }
                        else
                        {

                            wf.Remark = "正常加班";

                        }




                        wfi.AddInStanceOfHR(2, id);//添加流转步骤二   转人事审批

                        db.WorkFlows.Add(wf);
                        db.SaveChanges();

                    }
                    #endregion

                    #region 生产工人加班流程：部门同意 然后到生管同意 （如果是1.5倍 直接生成加班单，如果是2倍就需要推到人事同意）
                    else
                    {

                        //wfi.AddInstanceByManager(staffid, id);
                        //var pro_admin = db.SysInfo.FirstOrDefault().LinkMan;



                        //#region 判断日期是否为周六和周日 如果为周六和周日 那么将签核流程推给人事


                        //if (days == DayOfWeek.Sunday || days == DayOfWeek.Saturday)
                        //{
                        //    wfi.AddInstanceByUser(2, pro_admin, id);
                        //    wfi.AddInStanceOfHR(3, id);
                        //    wf.Remark = "假日加班";
                        //}
                        //else
                        //{
                        //    wfi.AddInstanceByUserLast(2, pro_admin, id);
                        //    wf.Remark = "正常加班";

                        //}


                        //#endregion

                        //db.WorkFlows.Add(wf);
                        //db.SaveChanges();

                    }
                    #endregion

                    return Ok(new { msg = "申请成功" });

                }
                else
                {

                    #region 经理加班流程
                    if (days == DayOfWeek.Sunday || days == DayOfWeek.Saturday)
                    {

                        wf.Remark = "假日加班";
                    }
                    else
                    {

                        wf.Remark = "正常加班";

                    }

                    wfi.AddInstanceByUserLast(1, emp.CardNo2, id);
                    return Ok(new { msg = "申请成功" });
                    #endregion

                }


            }
            else
            {

                return Ok(new { msg = "无需多次添加操作" });
            }

            #endregion



        }

        [HttpPost]

        public IActionResult otime(string date, string begin,string end,string[] lst)
        {
            string infos = "产线安排加班";
           
           




            #region  日期转换





            string dateStringb =date+begin;
            string dateStringe = date+end;

            DateTime dt = DateTime.ParseExact(date, "yyyy-MM-dd", System.Globalization.CultureInfo.CurrentCulture);
            DateTime dtb = DateTime.ParseExact(dateStringb, "yyyy-MM-ddHHmm", System.Globalization.CultureInfo.CurrentCulture);
            DateTime dte = DateTime.ParseExact(dateStringe, "yyyy-MM-ddHHmm", System.Globalization.CultureInfo.CurrentCulture);

            #region 判断是否存在同样的申请

            foreach (var item in lst)
            {
                string staffid = item;

                var days = dt.DayOfWeek;

                var emp = db.EmEmpData.Where(c => c.EmpNo == staffid).FirstOrDefault();
                WorkFlows wf = new WorkFlows();
                wf.Applydate = System.DateTime.Now;
                wf.Begindate = dtb;
                wf.Enddate = dte;
                wf.Reason = infos;



                //MemoryStream ms = new MemoryStream();
                //f.CopyTo(ms);
                //ms.Flush(); //非常重要的信息  不刷进去 它里面是空的
                //wf.Sign = ms.ToArray();
                string content = emp.Fname + " " + staffid + "申请加班" + "时间由" + dtb.ToString() + "到" + dte.ToString();
                #region 添加加班工作流实例
                string id = Guid.NewGuid().ToString();
                wf.Staffid = staffid;
                wf.FlowId = id;
                wf.FlowContent = content;
                wf.States = "发起";
                wf.Type = "加班";
                wf.IsComplete = false;

                var IsExit = db.WorkFlows.Any(c => c.Staffid == staffid && c.FlowContent == content);

                if (!IsExit)
                {
                    #endregion
                    if (emp.CardNo2 == "" || emp.CardNo2 == null)//如果不是经理级别
                    {

                        var dept = db.EsDept.Where(c => c.FullCode == emp.Dept).FirstOrDefault();

                        #region 职员加班流程：判断是否时月薪如果是，  不需要生管同意，部门同意以后，直接部门到人事
                        if (emp.Jxfs == "月薪")
                        {



                            var nid = wfi.AddInstanceByManager(staffid, id);// 添加流转步骤一  转经理审批
                                                                            //msg.SendMsg("钉钉提示您，泰德小程序内OA项目 有一项待您核签，", staffid);


                            if (days == DayOfWeek.Sunday || days == DayOfWeek.Saturday)
                            {

                                wf.Remark = "假日加班";
                            }
                            else
                            {

                                wf.Remark = "正常加班";

                            }




                            wfi.AddInStanceOfHR(2, id);//添加流转步骤二   转人事审批

                            db.WorkFlows.Add(wf);
                            db.SaveChanges();

                        }
                        #endregion

                        #region 生产工人加班流程：部门同意 然后到生管同意 （如果是1.5倍 直接生成加班单，如果是2倍就需要推到人事同意）
                        else
                        {

                            wfi.AddInstanceByManager(staffid, id);
                            var pro_admin = db.SysInfo.FirstOrDefault().LinkMan;



                            #region 判断日期是否为周六和周日 如果为周六和周日 那么将签核流程推给人事


                            if (days == DayOfWeek.Sunday || days == DayOfWeek.Saturday)
                            {
                                wfi.AddInstanceByUser(2, pro_admin, id);
                                wfi.AddInStanceOfHR(3, id);
                                wf.Remark = "假日加班";
                            }
                            else
                            {
                                wfi.AddInstanceByUserLast(2, pro_admin, id);
                                wf.Remark = "正常加班";

                            }


                            #endregion

                            db.WorkFlows.Add(wf);
                            db.SaveChanges();

                        }
                        #endregion

                      

                    }
                    else
                    {

                        #region 经理加班流程
                        if (days == DayOfWeek.Sunday || days == DayOfWeek.Saturday)
                        {

                            wf.Remark = "假日加班";
                        }
                        else
                        {

                            wf.Remark = "正常加班";

                        }

                        wfi.AddInstanceByUserLast(1, emp.CardNo2, id);

                        #endregion

                    }


                }
                else
                {

                  
                }



            }
            #endregion


            return Ok(new { msg = "添加成功" });
            #endregion






        }

        //调用

        public void token()
        {
            IDingTalkClient client = new DefaultDingTalkClient("https://oapi.dingtalk.com/gettoken");
            OapiGettokenRequest req = new OapiGettokenRequest();
            req.Appkey = "ding5ibuqempqoqfzykn";//"dingt691drnrkopwdusb";
            req.Appsecret = "fctZRJClnx8eFiWgEbzUfKWyldx82sZTXxeAg5HKqubvb64oMMjwtj6fJgO2rIRj";//"zIH8e2rQrPxDglVHy21VZXoU-Y9CjQ581QjhRm2eA40gfIBQey2tyyg-sLEnz09e";
            req.SetHttpMethod("GET");
            OapiGettokenResponse rsp = client.Execute(req, access_token);
            Console.WriteLine(rsp.Body);
            // return Ok(new {token=rsp.AccessToken });
            access_token = rsp.AccessToken;
        }
        [HttpGet]
        public ActionResult gettokenstr()
        {
            token();
            return Ok(new { token = access_token });
        }
        //获取Token缓存 数据，缓存token
        public string get_token()
        {
            var tokenstr = Cache.Get("token");
            if (tokenstr == null)
            {
                token();
                Cache.Set("token", access_token, TimeSpan.FromSeconds(7100));
                return access_token;
            }
            else
            {


                return tokenstr.ToString();

            }


        }
        public string getuserinfo(string code)
        {

            //token();
            IDingTalkClient client = new DefaultDingTalkClient("https://oapi.dingtalk.com/user/getuserinfo");
            OapiUserGetuserinfoRequest req = new OapiUserGetuserinfoRequest();
            req.Code = code;
            req.SetHttpMethod("GET");
            rsp = client.Execute(req, get_token());
            Console.WriteLine(rsp.Body);
            string uid = "";
            JObject jo = (JObject)JsonConvert.DeserializeObject(rsp.Body);
            try
            {
                name = jo["name"].ToString();
                uid = jo["userid"].ToString();
                Console.WriteLine(uid);
            }
            catch
            {

                name = "";
                userid = "";


            }
            return uid;


        }
        [HttpGet]
        public IActionResult test()
        {

            return Ok(new { msg = "test" });
        }
        // 今年年假信息
        [HttpGet]
        public IActionResult yearholiady(string staffid)
        {
            var year = DateTime.UtcNow.ToString("yyyy");
            var y = Int32.Parse(year);

            //   var  yday=db.EmEmpData.Include(c=>c.km)
            var emp = db.EmEmpData.Where(c => c.EmpNo == staffid).FirstOrDefault();
            var yday = db.KmNianJia.Where(c => c.EmpId == emp.EmpId && c.NYear == y).FirstOrDefault();
            if (yday.IUseInput == null)
            {
                yday.IUseInput = 0;

            }
            if (yday.IUseCalc == null)
            {
                yday.IUseCalc = 0;

            }


            return Ok(yday);

        }
        //上一年度年假信息
        [HttpGet]
        public IActionResult preyearholiady(string staffid)
        {
            var year = DateTime.UtcNow.ToString("yyyy");
            var y = Int32.Parse(year) - 1;
            //   var  yday=db.EmEmpData.Include(c=>c.km)
            var emp = db.EmEmpData.Where(c => c.EmpNo == staffid).FirstOrDefault();
            var yday = db.KmNianJia.Where(c => c.EmpId == emp.EmpId && c.NYear == y).FirstOrDefault();
            if (yday.IUseInput == null)
            {
                yday.IUseInput = 0;

            }
            if (yday.IUseCalc == null)
            {
                yday.IUseCalc = 0;

            }


            return Ok(yday);

        }
       //工时核签
        [HttpPost]
        public IActionResult upload_image(string staffid, string year, string month)
        {


            var oo = db.WorktimeConfrim.Where(c => c.Userid == staffid && c.Year == year && c.Month == month).FirstOrDefault();
            if (oo == null)
            {

                var f = Request.Form.Files["img"];

                WorktimeConfrim wt = new WorktimeConfrim();
                wt.Month = month;
                wt.Year = year;
                wt.Userid = staffid;

                MemoryStream ms = new MemoryStream();
                f.CopyTo(ms);
                ms.Flush(); //非常重要的信息  不刷进去 它里面是空的
                wt.Image = ms.ToArray();



                DateTime dt = DateTime.Now.AddMonths(-1);
                //本月第一天时间    
                DateTime dt_First = dt.AddDays(-(dt.Day) + 1);
                var first = dt_First.ToString("yyyy-MM-dd");
                //将本月月数+1  
                DateTime dt2 = dt.AddMonths(1);
                //本月最后一天时间  
                DateTime dt_Last = dt2.AddDays(-(dt.Day));
                var last = dt_Last.ToString("yyyy-MM-dd");

                var emp = db.EmEmpData.Where(c => c.EmpNo == staffid).FirstOrDefault();
                var empid = emp.EmpId;




                string sqlstr = string.Format(@"Select EmpName=E.FName,E.PinYin,E.CardNo,E.PyDate,E.LzDate,E.LzType,E.Dept,sDept=D.FName,
                sDeptDisplay=D.DisplayName,sBanZu=Z.FName,sZhiWu=W.FName,E.GangWei,sGangWei=GW.FName,sZhiJi=J.FName,E.WorkType,E.Jxfs,E.Account,Q.*
                , E_ks = E.E_ks, E_BanZu = E.E_BanZu, LzPzDate = E.LzPzDate, E_gzkpjbz = E.E_gzkpjbz, E_tsgwjtbz = E.E_tsgwjtbz, Memo = E.Memo, E_xljbz = E.E_xljbz
                From(Select Q.EmpID, Q.EmpNo, Q.Dept, Q.ZhiWu, Q.ZhiJi, Q.EmpType, YcqSj = Sum(IsNull(YcqSj, 0)), KgSj = Sum(IsNull(KgSj, 0)),
                CdSj = Sum(IsNull(CdSj, 0)), ZsCdSj = Sum(IsNull(ZsCdSj, 0)),
                ZtSj = Sum(IsNull(ZtSj, 0)), ZsZtSj = Sum(IsNull(ZsZtSj, 0)), ZbJbSj = Sum(IsNull(ZbJbSj, 0)), JbSj = Sum(IsNull(JbSj, 0)),
                YxjSj = Sum(IsNull(YxjSj, 0)), WxjSj = Sum(IsNull(WxjSj, 0)), KgCs = Sum(IsNull(KgCs, 0)), CdCs = Sum(IsNull(CdCs, 0)), ZtCs = Sum(IsNull(ZtCs, 0)),
                Ycqgs = Sum(Isnull(Ycqgs, 0)), ZsKgSj = Sum(IsNull(ZsKgSj, 0)), Sjcqgs = Sum(IsNull(Sjcqgs, 0)), JbSj1 = Sum(Isnull(Jbsj1, 0)),
                JbSj2 = Sum(IsNull(JbSj2, 0)), JbSj3 = Sum(IsNull(JbSj3, 0))
                , K_QqSj = Sum(IsNull(Q.K_QqSj, 0)), K_jbsq = Sum(IsNull(Q.K_jbsq, 0)), K_jbxs = Sum(IsNull(Q.K_jbxs, 0)), K_scqzb = Sum(IsNull(Q.K_scqzb, 0)),
                K_zgs = Sum(IsNull(Q.K_zgs, 0)), K_cqts = Sum(IsNull(Q.K_cqts, 0)), K_bzycq = Sum(IsNull(Q.K_bzycq, 0)), K_ybts = Sum(IsNull(Q.K_ybts, 0)),
                K_lxsbts = Sum(IsNull(Q.K_lxsbts, 0)), K_ktxjb = Sum(IsNull(Q.K_ktxjb, 0)), K_bkcs = Sum(IsNull(Q.K_bkcs, 0)), K_cd1d = Sum(IsNull(Q.K_cd1d, 0)),
                K_cd2d = Sum(IsNull(Q.K_cd2d, 0)), K_cd3d = Sum(IsNull(Q.K_cd3d, 0)), K_cd4d = Sum(IsNull(Q.K_cd4d, 0)),
                K_sffj = Sum(IsNull(Q.K_sffj, 0)), K_gzkpmf = Sum(IsNull(Q.K_gzkpmf, 0)), K_gzkpdf = Sum(IsNull(Q.K_gzkpdf, 0)), K_jbbd = Sum(IsNull(Q.K_jbbd, 0)),
                K_sjtrzgs = Sum(IsNull(Q.K_sjtrzgs, 0)), Q_bj = Sum(IsNull(Q.Q_bj, 0)),
                Q_cc = Sum(IsNull(Q.Q_cc, 0)), Q_nj = Sum(IsNull(Q.Q_nj, 0)), Q_gsj = Sum(IsNull(Q.Q_gsj, 0)), 
                Q_khj = Sum(IsNull(Q.Q_khj, 0)), Q_sanj = Sum(IsNull(Q.Q_sanj, 0)), Q_hj = Sum(IsNull(Q.Q_hj, 0)),
                Q_cj = Sum(IsNull(Q.Q_cj, 0)), Q_pcj = Sum(IsNull(Q.Q_pcj, 0)), Q_txj = Sum(IsNull(Q.Q_txj, 0)), Q_sj = Sum(IsNull(Q.Q_sj, 0)), Q_kg = Sum(IsNull(Q.Q_kg, 0))
                From KmResult Q Inner Join EmEmpData E on Q.EmpID = E.EmpID
                Left Join EsDept D ON Q.Dept = D.FullCode
                Left Join EsZhiWu W ON Q.ZhiWu = W.Code
                Left Join EsZhiJi J On Q.ZhiJi = J.Code
                Where Q.FDate between '{0}' and  ' {1}'
                Group By Q.EmpID, Q.EmpNo, Q.Dept, Q.ZhiWu, Q.ZhiJi, Q.EmpType) As Q
                Inner Join EmEmpData E ON Q.EmpID = E.EmpID
                Left Join EsDept D ON Q.Dept = D.FullCode
                Left Join EsZhiWu W ON Q.ZhiWu = W.Code
                LEFT JOIN EsGangWei GW ON E.GangWei = GW.Code
                Left Join EsZhiJi J On Q.ZhiJi = J.Code
                Left Join KsBanZu Z ON E.BanZu = Z.Code
                Where 1 = 1 AND E.EmpID = '{2}' Order By Q.EmpNo", first, last, empid);




                SqlCommand cmd = new SqlCommand();

                cmd.Connection = (SqlConnection)db.Database.GetDbConnection();
                cmd.CommandText = sqlstr;

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                adapter.Fill(table);
                year = dt_First.Year.ToString();
                month = dt_First.Month.ToString();


                wt.Dept = table.Rows[0][7].ToString();
                wt.Zhiwu = table.Rows[0][10].ToString();
                wt.Paydate = table.Rows[0][3].ToString();
                wt.Paytype = table.Rows[0][15].ToString();
                wt.Workhour = table.Rows[0][36].ToString();//应出勤工时
                wt.Minlate = table.Rows[0][25].ToString();//迟到时间
                wt.Minealry = table.Rows[0][27].ToString();//早退时间
                wt.Outduty = table.Rows[0][24].ToString();//旷工时间
                wt.Overtime = table.Rows[0][30].ToString();//1.5倍正班加班时间
                wt.Yearleave = table.Rows[0][64].ToString();//年休假
                wt.Adjustleave = table.Rows[0][71].ToString();//调休假
                wt.Deadleave = table.Rows[0][67].ToString();//丧假
                wt.Nopayleave = table.Rows[0][60].ToString();//事假
                wt.Sickleave = table.Rows[0][62].ToString();//病假
                wt.Marryleave = table.Rows[0][68].ToString();//婚假
                wt.Babyleave = table.Rows[0][69].ToString();//产假
                wt.Babykeperleave = table.Rows[0][70].ToString();//陪产假
                wt.Workheartleave = table.Rows[0][65].ToString();//工伤假
                wt.Watchleave = table.Rows[0][66].ToString(); //看护假
                wt.Doubleot = table.Rows[0][40].ToString();//2倍加班时间
                wt.Thrdot = table.Rows[0][41].ToString();//3.倍加班时间
                wt.Shouldtime = table.Rows[0][36].ToString();//应出勤正班时间
                wt.Truetime = table.Rows[0][45].ToString();//实际出勤工时
                wt.Totaltime = table.Rows[0][46].ToString();//总工时
                wt.Otapply = table.Rows[0][43].ToString();//加班申请
                wt.Confirms = "已经核签";




                db.WorktimeConfrim.Add(wt);
                db.SaveChanges();
                ms.Dispose();


                return Ok(new { msg = "ok" });

            }

            return Ok(new { msg = "ok" });

        }
        //钉钉发送通知信息

        //绑定工号
        [HttpGet]
        public IActionResult userbind(string staffid, string code)
        {
            string uid = "";
            if (access_token == "" || access_token == string.Empty)
            {
                token();


            }


            if (rsp == null)
            {

                uid = getuserinfo(code);

            }

            if (name == "" || name == string.Empty)
            {
                return Ok(new { msg = "error" });
            }


            var oo = db.EmEmpData.Where(c => c.EmpNo == staffid).FirstOrDefault();
            if (oo != null && oo.Fname == name)
            {
                if (uid != null && uid != "")
                {
                    oo.DuserName = uid;
                    db.EmEmpData.Update(oo);
                    db.SaveChanges();
                    return Ok(new { msg = "绑定成功", fname = oo.Fname, userid = uid });

                }
                else
                {

                    return Ok(new { msg = "error" });

                }



            }
            else
            {

                return Ok(new { msg = "error" });

            }


        }
        //通过数据库获取工时
        [HttpGet]
        public IActionResult worktime(string staffid)
        {

            DateTime dt = DateTime.Now.AddMonths(-1);
            //本月第一天时间    
            DateTime dt_First = dt.AddDays(-(dt.Day) + 1);
            var first = dt_First.ToString("yyyy-MM-dd");
            //将本月月数+1  
            DateTime dt2 = dt.AddMonths(1);
            //本月最后一天时间  
            DateTime dt_Last = dt2.AddDays(-(dt.Day));
            var last = dt_Last.ToString("yyyy-MM-dd");

            var emp = db.EmEmpData.Where(c => c.EmpNo == staffid).FirstOrDefault();
            var empid = emp.EmpId;




            string sqlstr = string.Format(@"Select EmpName=E.FName,E.PinYin,E.CardNo,E.PyDate,E.LzDate,E.LzType,E.Dept,sDept=D.FName,
                sDeptDisplay=D.DisplayName,sBanZu=Z.FName,sZhiWu=W.FName,E.GangWei,sGangWei=GW.FName,sZhiJi=J.FName,E.WorkType,E.Jxfs,E.Account,Q.*
                , E_ks = E.E_ks, E_BanZu = E.E_BanZu, LzPzDate = E.LzPzDate, E_gzkpjbz = E.E_gzkpjbz, E_tsgwjtbz = E.E_tsgwjtbz, Memo = E.Memo, E_xljbz = E.E_xljbz
                From(Select Q.EmpID, Q.EmpNo, Q.Dept, Q.ZhiWu, Q.ZhiJi, Q.EmpType, YcqSj = Sum(IsNull(YcqSj, 0)), KgSj = Sum(IsNull(KgSj, 0)),
                CdSj = Sum(IsNull(CdSj, 0)), ZsCdSj = Sum(IsNull(ZsCdSj, 0)),
                ZtSj = Sum(IsNull(ZtSj, 0)), ZsZtSj = Sum(IsNull(ZsZtSj, 0)), ZbJbSj = Sum(IsNull(ZbJbSj, 0)), JbSj = Sum(IsNull(JbSj, 0)),
                YxjSj = Sum(IsNull(YxjSj, 0)), WxjSj = Sum(IsNull(WxjSj, 0)), KgCs = Sum(IsNull(KgCs, 0)), CdCs = Sum(IsNull(CdCs, 0)), ZtCs = Sum(IsNull(ZtCs, 0)),
                Ycqgs = Sum(Isnull(Ycqgs, 0)), ZsKgSj = Sum(IsNull(ZsKgSj, 0)), Sjcqgs = Sum(IsNull(Sjcqgs, 0)), JbSj1 = Sum(Isnull(Jbsj1, 0)),
                JbSj2 = Sum(IsNull(JbSj2, 0)), JbSj3 = Sum(IsNull(JbSj3, 0))
                , K_QqSj = Sum(IsNull(Q.K_QqSj, 0)), K_jbsq = Sum(IsNull(Q.K_jbsq, 0)), K_jbxs = Sum(IsNull(Q.K_jbxs, 0)), K_scqzb = Sum(IsNull(Q.K_scqzb, 0)),
                K_zgs = Sum(IsNull(Q.K_zgs, 0)), K_cqts = Sum(IsNull(Q.K_cqts, 0)), K_bzycq = Sum(IsNull(Q.K_bzycq, 0)), K_ybts = Sum(IsNull(Q.K_ybts, 0)),
                K_lxsbts = Sum(IsNull(Q.K_lxsbts, 0)), K_ktxjb = Sum(IsNull(Q.K_ktxjb, 0)), K_bkcs = Sum(IsNull(Q.K_bkcs, 0)), K_cd1d = Sum(IsNull(Q.K_cd1d, 0)),
                K_cd2d = Sum(IsNull(Q.K_cd2d, 0)), K_cd3d = Sum(IsNull(Q.K_cd3d, 0)), K_cd4d = Sum(IsNull(Q.K_cd4d, 0)),
                K_sffj = Sum(IsNull(Q.K_sffj, 0)), K_gzkpmf = Sum(IsNull(Q.K_gzkpmf, 0)), K_gzkpdf = Sum(IsNull(Q.K_gzkpdf, 0)), K_jbbd = Sum(IsNull(Q.K_jbbd, 0)),
                K_sjtrzgs = Sum(IsNull(Q.K_sjtrzgs, 0)), Q_bj = Sum(IsNull(Q.Q_bj, 0)),
                Q_cc = Sum(IsNull(Q.Q_cc, 0)), Q_nj = Sum(IsNull(Q.Q_nj, 0)), Q_gsj = Sum(IsNull(Q.Q_gsj, 0)), 
                Q_khj = Sum(IsNull(Q.Q_khj, 0)), Q_sanj = Sum(IsNull(Q.Q_sanj, 0)), Q_hj = Sum(IsNull(Q.Q_hj, 0)),
                Q_cj = Sum(IsNull(Q.Q_cj, 0)), Q_pcj = Sum(IsNull(Q.Q_pcj, 0)), Q_txj = Sum(IsNull(Q.Q_txj, 0)), Q_sj = Sum(IsNull(Q.Q_sj, 0)), Q_kg = Sum(IsNull(Q.Q_kg, 0))
                From KmResult Q Inner Join EmEmpData E on Q.EmpID = E.EmpID
                Left Join EsDept D ON Q.Dept = D.FullCode
                Left Join EsZhiWu W ON Q.ZhiWu = W.Code
                Left Join EsZhiJi J On Q.ZhiJi = J.Code
                Where Q.FDate between '{0}' and  ' {1}'
                Group By Q.EmpID, Q.EmpNo, Q.Dept, Q.ZhiWu, Q.ZhiJi, Q.EmpType) As Q
                Inner Join EmEmpData E ON Q.EmpID = E.EmpID
                Left Join EsDept D ON Q.Dept = D.FullCode
                Left Join EsZhiWu W ON Q.ZhiWu = W.Code
                LEFT JOIN EsGangWei GW ON E.GangWei = GW.Code
                Left Join EsZhiJi J On Q.ZhiJi = J.Code
                Left Join KsBanZu Z ON E.BanZu = Z.Code
                Where 1 = 1 AND E.EmpID = '{2}' Order By Q.EmpNo", first, last, empid);



            //SqlConnection conn = new SqlConnection();
            ////conn.ConnectionString = db.Database.Connection.ConnectionString;

            //conn.ConnectionString = "Data Source = 192.168.10.26; Initial Catalog = EasyStar; User ID = sa; Password = yp#@123;MultipleActiveResultSets=true";

            //if (conn.State != ConnectionState.Open)
            //{
            //    conn.Open();
            //}
            SqlCommand cmd = new SqlCommand();

            cmd.Connection = (SqlConnection)db.Database.GetDbConnection();
            cmd.CommandText = sqlstr;

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            string year = dt_First.Year.ToString();
            string month = dt_First.Month.ToString();

            var confrm = db.WorktimeConfrim.Where(c => c.Userid == staffid.ToString() && c.Year == year && c.Month == month && c.Image != null).FirstOrDefault();
            if (confrm != null)
            {
                return Ok(new
                {


                    dept = table.Rows[0][7].ToString(),
                    zhiwu = table.Rows[0][10].ToString(),
                    paydate = table.Rows[0][3].ToString(),
                    paytype = table.Rows[0][15].ToString(),
                    workhour = table.Rows[0][36].ToString(),//应出勤工时
                    minlate = table.Rows[0][25].ToString(),//迟到时间
                    minealry = table.Rows[0][27].ToString(),//早退时间
                    outduty = table.Rows[0][24].ToString(),//旷工时间
                    overtime1 = table.Rows[0][30].ToString(),//1.5倍正班加班时间
                    yearleave = table.Rows[0][64].ToString(),//年休假
                    adjustleave = table.Rows[0][71].ToString(),//调休假
                    deadleave = table.Rows[0][67].ToString(),//丧假
                    nopayleave = table.Rows[0][60].ToString(),//事假
                    sickleave = table.Rows[0][62].ToString(),//病假
                    marryleave = table.Rows[0][68].ToString(),//婚假
                    babyleave = table.Rows[0][69].ToString(),//产假
                    babykeperleave = table.Rows[0][70].ToString(),//陪产假
                    workheartleave = table.Rows[0][65].ToString(),//工伤假
                    watchleave = table.Rows[0][66].ToString(), //看护假
                    doubleot = table.Rows[0][40].ToString(),//2倍加班时间
                    thrdot = table.Rows[0][41].ToString(),//3.倍加班时间
                    shouldtime = table.Rows[0][36].ToString(),//应出勤正班时间
                    truetime = table.Rows[0][45].ToString(),//实际出勤工时
                    totaltime = table.Rows[0][46].ToString(),//总工时
                    otapply = table.Rows[0][43].ToString(),//加班申请
                    confirm = true,
                    _year = year,
                    _month = month,

                });


            }
            else
            {

                return Ok(new
                {


                    dept = table.Rows[0][7].ToString(),
                    zhiwu = table.Rows[0][10].ToString(),
                    paydate = table.Rows[0][3].ToString(),
                    paytype = table.Rows[0][15].ToString(),
                    workhour = table.Rows[0][36].ToString(),//应出勤工时
                    minlate = table.Rows[0][25].ToString(),//迟到时间
                    minealry = table.Rows[0][27].ToString(),//早退时间
                    outduty = table.Rows[0][24].ToString(),//旷工时间
                    overtime1 = table.Rows[0][30].ToString(),//1.5倍正班加班时间
                    yearleave = table.Rows[0][64].ToString(),//年休假
                    adjustleave = table.Rows[0][71].ToString(),//调休假
                    deadleave = table.Rows[0][67].ToString(),//丧假
                    nopayleave = table.Rows[0][60].ToString(),//事假
                    sickleave = table.Rows[0][62].ToString(),//病假
                    marryleave = table.Rows[0][68].ToString(),//婚假
                    babyleave = table.Rows[0][69].ToString(),//产假
                    babykeperleave = table.Rows[0][70].ToString(),//陪产假
                    workheartleave = table.Rows[0][65].ToString(),//工伤假
                    watchleave = table.Rows[0][66].ToString(), //看护假
                    doubleot = table.Rows[0][40].ToString(),//2倍加班时间
                    thrdot = table.Rows[0][41].ToString(),//3.倍加班时间
                    shouldtime = table.Rows[0][36].ToString(),//应出勤正班时间
                    truetime = table.Rows[0][45].ToString(),//实际出勤工时
                    totaltime = table.Rows[0][46].ToString(),//总工时
                    otapply = table.Rows[0][43].ToString(),//加班申请
                    confirm = false,
                    _year = year,
                    _month = month,


                });

            }




        }

        public IActionResult worktimes(string staffid)
        {
            List<wtime> mylist = new List<wtime>();
            DateTime dt = DateTime.Now;
            //本月第一天时间    
            DateTime dt1 = DateTime.Now.AddDays(-30);
            var oo = db.EmEmpData.Where(c => c.EmpNo == staffid).FirstOrDefault();
            var oso = db.KmResult.Where(c => c.EmpId == oo.EmpId && c.Fdate >= dt1 && c.Fdate <= dt).ToList();
            foreach (var itm in oso)
            {

                var op = new wtime();
                op.date = itm.Fdate.ToString("yyyy-MM-dd");
                op.sb1 = gettime(itm.B1sb);
                op.xb1 = gettime(itm.B1xb);
                op.sb2 = gettime(itm.B2sb);
                op.xb2 = gettime(itm.B2xb);
                op.sb3 = gettime(itm.B3sb);
                op.xb3 = gettime(itm.B3xb);
                op.weekday = getweek(itm.Fdate);
                op.shouldtimehour = itm.Ycqgs.ToString();
                op.truetimehour = itm.Sjcqgs.ToString();
                op.shouldtimemin = itm.YcqSj.ToString();
                op.outtime = itm.KgSj.ToString();
                op.earlytime = itm.ZtSj.ToString();
                op.leatertime = itm.CdSj.ToString();

                mylist.Add(op);

            }
            mylist.Reverse();

            return Ok(mylist);


        }
        public string getweek(DateTime dt)
        {
            var os = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.GetDayName(dt.DayOfWeek);
            return os;
        }

        public string gettime(short? oo)
        {
            if (oo == 0 || oo == null)
            {
                return " ";
            }
            var tm = Int32.Parse(oo.ToString());
            var h = (tm / 60).ToString().PadLeft(2, '0');
            var m = (tm % 60).ToString().PadLeft(2, '0');
            return h + ':' + m;

        }
    }
    public class wtime
    {
        public string sb1 { get; set; }
        public string sb2 { get; set; }
        public string sb3 { get; set; }
        public string xb1 { get; set; }
        public string xb2 { get; set; }
        public string xb3 { get; set; }
        public string shouldtimehour { get; set; }
        public string truetimehour { get; set; }

        public string shouldtimemin { get; set; }

        public string date { get; set; }
        public string weekday { get; set; }

        public string leatertime { get; set; }
        public string outtime { get; set; }

        public string earlytime { get; set; }
    }




}